from pymysql import connect, Error
from pymysql.cursors import DictCursor


# 使用面向对象的方式来优化代码

class PyMySQLLearn(object):

    def __init__(self):
        #     数据库的连接对象
        self.conn = self.get_connection()

    def get_connection(self):
        # 获取数据库的连接对象
        try:
            conn = connect(
                host='127.0.0.1',
                port=3306,
                user='root',
                password='123456',
                database='my_news',
                # 将结果转换为字典
                cursorclass=DictCursor
            )
            return conn
        except Error as e:
            print('连接失败：{}'.format(e))
            return None

    def close_connection(self):
        #         关闭数据库连接
        try:
            if self.conn is not None:
                self.conn.close()
        except Error as e:
            print('数据库关闭失败：{}'.format(e))

    def get_one_data(self):
        #         检索一条数据
        class_name = '三年级一班'
        stu_name = '王五'
        sql = 'SELECT * FROM school_student_info where class_name= %s and stu_name=%s;'
        with self.conn.cursor() as cursor:
            cursor.execute(sql, (class_name, stu_name))
            result = cursor.fetchone()
        return result

    def delete_one_data(self):
        #         删除一条数据
        id = 16
        sql = 'DELETE FROM school_student_info WHERE id=%s'
        with self.conn.cursor() as cursor:
            cursor.execute(sql, (id,))
            self.conn.commit()
            print('删除成功')

    def delete_many_data(self):
        #         删除多条数据
        dataList = ((14,), (13,))
        sql = 'DELETE FROM school_student_info WHERE id=%s'
        with self.conn.cursor() as cursor:
            cursor.executemany(sql, dataList)
            self.conn.commit()
            print('删除成功')


def main():
    obj = PyMySQLLearn()
    # result = obj.get_one_data()
    # print(result)

    # obj.delete_one_data()
    obj.delete_many_data()

    #     关闭连接
    obj.close_connection()


if __name__ == '__main__':
    main()
